﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TalentPools.DAL;
using TalentPools.Model;

namespace TalentPools.DAL
{
    public class SearchResumeService
    {
        /// <summary>
        /// 根据查询条件搜索简历，返回分页后的数据集对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="workCatagory"></param>
        /// <param name="workArea"></param>
        /// <param name="workYear"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public DataSet GetResumeByPage(int pageIndex, int pageSize, string workCatagory, string workArea, string workYear, string searchKey)
        {
            StringBuilder build = new StringBuilder();
            string sql1 = @"select * from(select ROW_NUMBER() over(order by lastUpdateTime)RowIndex, resume.Id,resume.workYear,resume.sex,Skill.Name,intention.profession,intention.area from resume,Skill,intention
where resume.isPublic=1 and resume.isPb=0 and resume.Id=intention.resumeId and resume.Id=Skill.resumeId";
            string sql2 = " and (intention.industry like '%'+@keyword+'%' or Skill.Name like '%'+@keyword+'%' )) a where RowIndex  between @begin and +@end";
            build.Append(sql1);
            int begin = pageSize * (pageIndex - 1) + 1;
            int end = pageSize * pageIndex;
            string keyword = "%";
            if (searchKey != "")
            {
                keyword = searchKey;
            }
            if (workArea!="")
            {
                build.Append(" " + "and intention.area='" + workArea + "'");
            }
            if (workYear!="")
            {
                build.Append(" " + "and resume.WorkYear='" + workYear + "'");
            }
            if (workCatagory!="")
            {
                build.Append(" " + "and intention.profession='" + workCatagory + "'");
            }
            build.Append(" " + sql2);
            build=build.Replace('{',' ');
            build=build.Replace('}',' ');
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@keyword",searchKey),
                new SqlParameter("@begin",begin),
                new SqlParameter("@end",end),
            new SqlParameter("@PageIndex",pageIndex),
            new SqlParameter("@PageSize",pageSize)
            };
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text,build.ToString() , paras);
            return ds;
        }
        /// <summary>
        /// 根据查询条件，获取简历的总数量
        /// </summary>
        /// <param name="andCondition"></param>
        /// <returns></returns>
        public int GetResumeCount(string workCatagory, string workArea, string workYear, string searchKey)
        {
            StringBuilder build = new StringBuilder();
            string sql1 = @"select count(*) from resume,Skill,intention
where resume.isPublic=1 and resume.isPb=0 and resume.Id=intention.resumeId and resume.Id=Skill.resumeId";
            string sql2 = " and (intention.industry like '%'+@keyword+'%' or Skill.Name like '%'+@keyword+'%' )";
            build.Append(sql1);
            string keyword = "%";
            if (searchKey != "")
            {
                keyword = searchKey;
            }
            if (workArea != "")
            {
                build.Append(" " + "and intention.area='" + workArea + "'");
            }
            if (workYear != "")
            {
                build.Append(" " + "and resume.WorkYear='" + workYear + "'");
            }
            if (workCatagory != "")
            {
                build.Append(" " + "and intention.profession='" + workCatagory + "'");
            }
            build.Append(" " + sql2);
            build = build.Replace('{', ' ');
            build = build.Replace('}', ' ');
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@keyword",searchKey)
            };
            return Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text,build.ToString(),paras));
        }

        /// <summary>
        /// 根据查询条件搜索简历，返回分页后的数据集对象
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="workCatagory"></param>
        /// <param name="workArea"></param>
        /// <param name="workYear"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public DataSet GetResumeByPage1(int pageIndex, int pageSize, string workCatagory, string workArea, string workYear, string searchKey)
        {
            StringBuilder build = new StringBuilder();
            string sql1 = @"select * from(select ROW_NUMBER() over(order by lastUpdateTime)RowIndex, resume.Id,resume.workYear,resume.sex,Skill.Name,intention.profession,intention.area from resume,Skill,intention
where resume.isPublic=1 and resume.isPb=0 and resume.Id=intention.resumeId and resume.Id=Skill.resumeId";
            string sql2 = " and (intention.industry like '%'+@keyword+'%' or Skill.Name like '%'+@keyword+'%' )) a where RowIndex  between @begin and +@end";
            build.Append(sql1);
            int begin = pageSize * (pageIndex - 1) + 1;
            int end = pageSize * pageIndex;
            string keyword = "%";
            if (searchKey != "")
            {
                keyword = searchKey;
            }
            if (workArea != "")
            {
                build.Append(" " + "and intention.area='" + workArea + "'");
            }
            if (workYear != "")
            {
                build.Append(" " + "and resume.WorkYear='" + workYear + "'");
            }
            if (workCatagory != "")
            {
                build.Append(" " + "and intention.profession='" + workCatagory + "'");
            }
            build.Append(" " + sql2);
            build = build.Replace('{', ' ');
            build = build.Replace('}', ' ');
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@keyword",searchKey),
                new SqlParameter("@begin",begin),
                new SqlParameter("@end",end),
            new SqlParameter("@PageIndex",pageIndex),
            new SqlParameter("@PageSize",pageSize)
            };
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, build.ToString(), paras);
            return ds;
        }
        /// <summary>
        /// 根据查询条件，获取简历的总数量
        /// </summary>
        /// <param name="andCondition"></param>
        /// <returns></returns>
        public int GetResumeCount1(string workCatagory, string workArea, string workYear, string searchKey)
        {
            StringBuilder build = new StringBuilder();
            string sql1 = @"select count(*) from resume,Skill,intention
where resume.isPublic=1 and isPb=0 and resume.Id=intention.resumeId and resume.Id=Skill.resumeId";
            string sql2 = " and (intention.industry like '%'+@keyword+'%' or Skill.Name like '%'+@keyword+'%' )";
            build.Append(sql1);
            string keyword = "%";
            if (searchKey != "")
            {
                keyword = searchKey;
            }
            if (workArea != "")
            {
                build.Append(" " + "and intention.area='" + workArea + "'");
            }
            if (workYear != "")
            {
                build.Append(" " + "and resume.WorkYear='" + workYear + "'");
            }
            if (workCatagory != "")
            {
                build.Append(" " + "and intention.profession='" + workCatagory + "'");
            }
            build.Append(" " + sql2);
            build = build.Replace('{', ' ');
            build = build.Replace('}', ' ');
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@keyword",searchKey)
            };
            return Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, build.ToString(), paras));
        }
        /// <summary>
        /// 获取十五条记录
        /// </summary>
        /// <returns></returns>
        public DataSet GetHeadHuntPersonTop()
        {
            string sql = "select top 15 resume.Id,resume.sex,resume.workYear,intention.profession,intention.area,intention.industry,intention.resumeId from resume,intention where resume.Id=intention.resumeId and resume.isPublic=1 and resume.Istj=1 and isPb=0";
            using (DataSet ds=DBHelper.ExecuteDataSet(CommandType.Text,sql,null))
            {
                return ds;
            }
        }

        public DataSet GetHuntByPage(int pageIndex, int pageSize, string workCatagory, string workArea, string workYear, string searchKey)
        {
            StringBuilder build = new StringBuilder();
            string sql1 = @"select * from(select ROW_NUMBER() over(order by lastUpdateTime)RowIndex, resume.Id,resume.workYear,resume.sex,Skill.Name,intention.profession,intention.area from resume,Skill,intention
where resume.isPublic=1 and resume.Istj=1 and resume.isPb=0 and resume.Id=intention.resumeId and resume.Id=Skill.resumeId";
            string sql2 = " and (intention.industry like '%'+@keyword+'%' or Skill.Name like '%'+@keyword+'%' )) a where RowIndex  between @begin and +@end";
            build.Append(sql1);
            int begin = pageSize * (pageIndex - 1) + 1;
            int end = pageSize * pageIndex;
            string keyword = "%";
            if (searchKey != "")
            {
                keyword = searchKey;
            }
            if (workArea != "")
            {
                build.Append(" " + "and intention.area='" + workArea + "'");
            }
            if (workYear != "")
            {
                build.Append(" " + "and resume.WorkYear='" + workYear + "'");
            }
            if (workCatagory != "")
            {
                build.Append(" " + "and intention.profession='" + workCatagory + "'");
            }
            build.Append(" " + sql2);
            build = build.Replace('{', ' ');
            build = build.Replace('}', ' ');
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@keyword",searchKey),
                new SqlParameter("@begin",begin),
                new SqlParameter("@end",end),
            new SqlParameter("@PageIndex",pageIndex),
            new SqlParameter("@PageSize",pageSize)
            };
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, build.ToString(), paras);
            return ds;
        }


        public int GetHuntCount(string workCatagory, string workArea, string workYear, string searchKey)
        {
            StringBuilder build = new StringBuilder();
            string sql1 = @"select count(*) from resume,Skill,intention
where resume.isPublic=1 and resume.Istj=1 and resume.isPb=0 and resume.Id=intention.resumeId and resume.Id=Skill.resumeId";
            string sql2 = " and (intention.industry like '%'+@keyword+'%' or Skill.Name like '%'+@keyword+'%' )";
            build.Append(sql1);
            string keyword = "%";
            if (searchKey != "")
            {
                keyword = searchKey;
            }
            if (workArea != "")
            {
                build.Append(" " + "and intention.area='" + workArea + "'");
            }
            if (workYear != "")
            {
                build.Append(" " + "and resume.WorkYear='" + workYear + "'");
            }
            if (workCatagory != "")
            {
                build.Append(" " + "and intention.profession='" + workCatagory + "'");
            }
            build.Append(" " + sql2);
            build = build.Replace('{', ' ');
            build = build.Replace('}', ' ');
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@keyword",searchKey)
            };
            return Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, build.ToString(), paras));
        }

        public DataSet GetNewResume()
        {
            string sql = "select top 20 resume.Id,resume.workYear,resume.sex,Skill.Name,intention.profession,intention.area from resume,Skill,intention where resume.isPublic=1 and resume.isPb=0 and resume.Id=intention.resumeId and resume.Id=Skill.resumeId order by resume.LastupdateTime desc";
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, null);
        }
    }
}
